class Database {
  static engine;

  async #query(sql, ...params) {
    // 兼容 sql 模板字符串
    if (Array.isArray(sql))
      sql = String.raw({ raw: sql }, ...Array(params.length).fill("?"));
    else {
      if (params.length == 1 && Array.isArray(params[0])) [params] = params;
    }
    return await this.run(sql, params);
  }

  /**
   * 查询多条数据
   * @param {string | string[]} sql 查询语句
   * @param  {...any} params
   * @returns { Promise<any[]> }
   */
  async findMany(sql, ...params) {
    return this.#query(sql, ...params);
  }

  /**
   * 查询单条数据
   * @param {string | string[]} sql 查询语句
   * @param  {...any} params
   * @returns { Promise<any> }
   */
  async findOne(sql, ...params) {
    const [rs] = await this.#query(sql, ...params);
    return rs;
  }

  async insert(sql, ...params) {
    const { insertId } = await this.#query(sql, ...params);
    if (Array.isArray(sql))
      sql = String.raw({ raw: sql }, ...Array(params.length).fill("?"));
    return this.findOne(
      `select * from ${getTableName(sql)} where id = ${insertId}`
    );
  }

  async update(sql, ...params) {
    const { affectedRows } = await this.#query(sql, ...params);
    return affectedRows;
  }

  async delete(sql, ...params) {
    const { affectedRows } = await this.#query(sql, ...params);
    return affectedRows;
  }

  end() {}
}

class MySql extends Database {
  static engine = "mysql";

  constructor(pool) {
    super();
    this.pool = pool;
  }

  static async create(config) {
    const mysql = await import("mysql2");
    return new MySql(mysql.createPool(config));
  }

  async run(sql, params) {
    return new Promise((resolve, reject) => {
      this.pool.execute(sql, params, (err, results) => {
        if (err) return reject(err);
        resolve(results);
      });
    });
  }

  end() {
    this.pool.end();
  }
}

class Sqlite extends Database {
  static engine = "sqlite";

  constructor(db) {
    super();
    this.db = db;
  }

  static async create(filename) {
    const sqlite = await import("node:sqlite");
    const { DatabaseSync } = sqlite;
    const db = new DatabaseSync(filename);
    return new Sqlite(db);
  }

  async run(sql, params) {
    return this.db.run(sql, params);
  }

  end() {
    this.db.close();
  }
}

// 根据 insert 语句获取表名
function getTableName(sql) {
  // 正则表达式解析：
  // /INSERT\s+INTO\s+  匹配 INSERT INTO（忽略大小写，允许中间有多个空格）
  // (?:`([^`]+)`|     匹配反引号包裹的表名（如 `table`），捕获反引号内的内容
  //  "([^"]+)"|       匹配双引号包裹的表名（如 "table"）
  //  '([^']+)'|       匹配单引号包裹的表名（如 'table'）
  //  (\w+))           匹配无引号的表名（字母数字下划线）
  // \s*[\(;]          表名后紧跟空格和 ( 或 ;（结束标志）
  const regex =
    /INSERT\s+INTO\s+(?:`([^`]+)`|"([^"]+)"|'([^']+)'|(\w+))\s*[\(;]/i;

  const match = sql.match(regex);
  if (!match) return null;

  // 提取第一个非 null 的捕获组（表名可能在 1-4 组中）
  return match.slice(1).find((group) => group !== undefined);
}

// 测试用例
const db = await MySql.create({
  host: "localhost",
  user: "root",
  password: "",
  database: "demo",
  waitForConnections: true,
  connectionLimit: 10, //
});
const username = "李四";
const password = "111222";
const email = "7989777@qq.com";
// 插入语句
const results =
  await db.insert`insert into users(username, password, email) values (${username}, ${password}, ${email})`;
console.log(results);
// 查询语句
// 查询单条数据
const rs1 = await db.findOne`select * from users where id = ${results.id}`;
console.log(rs1);
// 查询多条数据
const rs2 = await db.findMany`select * from users`;
console.log(rs2);
// 更新语句
const rs3 =
  await db.update`update users set username = '王五' where id = ${results.id}`;
console.log(rs3);
// 删除语句
const rs4 = await db.delete`delete from users where id = ${results.id}`;
console.log(rs4);
// 关闭连接
db.end();
